package com.xky.ui.module.customersManagement.select;

import com.xky.pojo.Customers;
import com.xky.utils.DBUtils;
import lombok.Getter;
import lombok.Setter;

import javax.swing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Setter
@Getter
public class SelectCustomers {

    /**
     * 查询所有客户信息
     * @return 客户列表
     */
    public static List<Customers> selectAllCustomers() {
        Connection connection;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Customers> customersList = new ArrayList<>();

        connection = DBUtils.getConnection();
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM customers");
            resultSet = preparedStatement.executeQuery();
            

            while (resultSet.next()) {
                Customers customers = new Customers();
                customers.setCustomerId(resultSet.getInt("customer_id"));
                customers.setCustomerCode(resultSet.getString("customer_code"));
                customers.setCustomerName(resultSet.getString("customer_name"));
                customers.setContactPerson(resultSet.getString("contact_person"));
                customers.setPhone(resultSet.getString("phone"));
                customers.setEmail(resultSet.getString("email"));
                customers.setAddress(resultSet.getString("address"));
                customers.setCreditLimit(resultSet.getString("credit_limit"));
                customers.setIsActive(resultSet.getBoolean("is_active"));
                
                // 获取创建时间和更新时间
                if (resultSet.getTimestamp("created_time") != null) {
                    customers.setCreatedTime(resultSet.getTimestamp("created_time").toLocalDateTime());
                }
                if (resultSet.getTimestamp("updated_time") != null) {
                    customers.setUpdatedTime(resultSet.getTimestamp("updated_time").toLocalDateTime());
                }
                
                customersList.add(customers);
            }
            return customersList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
    }

    /**
     * 检查指定ID的客户是否存在
     * @param id 客户ID
     * @return 是否存在
     */
    public static Boolean checkCustomerExistsById(Integer id) {
        Connection connection;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection = DBUtils.getConnection();
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM customers WHERE customer_id = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return false;
    }

    /**
     * 根据ID查询客户信息
     * @param id 客户ID
     * @return 客户列表
     */
    public static List<Customers> selectCustomersById(Integer id) {
        Connection connection;
        PreparedStatement preparedStatement = null;//用于执行预编译的 SQL 语句。
        ResultSet resultSet = null;
        List<Customers> customersList = new ArrayList<>();

        connection = DBUtils.getConnection();
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM customers WHERE customer_id = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Customers customers = new Customers();
                customers.setCustomerId(resultSet.getInt("customer_id"));
                customers.setCustomerCode(resultSet.getString("customer_code"));
                customers.setCustomerName(resultSet.getString("customer_name"));
                customers.setContactPerson(resultSet.getString("contact_person"));
                customers.setPhone(resultSet.getString("phone"));
                customers.setEmail(resultSet.getString("email"));
                customers.setAddress(resultSet.getString("address"));
                customers.setCreditLimit(resultSet.getString("credit_limit"));
                customers.setIsActive(resultSet.getBoolean("is_active"));
                
                // 获取创建时间和更新时间
                if (resultSet.getTimestamp("created_time") != null) {
                    customers.setCreatedTime(resultSet.getTimestamp("created_time").toLocalDateTime());
                }
                if (resultSet.getTimestamp("updated_time") != null) {
                    customers.setUpdatedTime(resultSet.getTimestamp("updated_time").toLocalDateTime());
                }
                
                customersList.add(customers);
            }
            return customersList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
    }

    /**
     * 根据客户名称进行模糊查询
     * @param keyword 关键词
     * @return 客户列表
     */
    public static List<Customers> searchCustomersByKeyword(String keyword) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Customers> customersList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            // 使用LIKE进行模糊查询，搜索所有相关字段
            preparedStatement = connection.prepareStatement(
                "SELECT * FROM customers WHERE customer_id = ? " +
                "OR customer_code LIKE ? " +
                "OR customer_name LIKE ? " +
                "OR contact_person LIKE ? " +
                "OR phone LIKE ? " +
                "OR email LIKE ? " +
                "OR address LIKE ? " +
                "OR credit_limit LIKE ? " +
                "OR CAST(is_active AS CHAR) LIKE ?"
            );
            String searchPattern = "%" + keyword + "%";
            
            // Try to parse the keyword as an integer for customer_id
            try {
                int id = Integer.parseInt(keyword);
                preparedStatement.setInt(1, id);
            } catch (NumberFormatException e) {
                // If not a valid integer, set it to -1 (which won't match any ID)
                preparedStatement.setInt(1, -1);
            }
            
            preparedStatement.setString(2, searchPattern);
            preparedStatement.setString(3, searchPattern);
            preparedStatement.setString(4, searchPattern);
            preparedStatement.setString(5, searchPattern);
            preparedStatement.setString(6, searchPattern);
            preparedStatement.setString(7, searchPattern);
            preparedStatement.setString(8, searchPattern);
            preparedStatement.setString(9, searchPattern);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Customers customers = new Customers();
                customers.setCustomerId(resultSet.getInt("customer_id"));
                customers.setCustomerCode(resultSet.getString("customer_code"));
                customers.setCustomerName(resultSet.getString("customer_name"));
                customers.setContactPerson(resultSet.getString("contact_person"));
                customers.setPhone(resultSet.getString("phone"));
                customers.setEmail(resultSet.getString("email"));
                customers.setAddress(resultSet.getString("address"));
                customers.setCreditLimit(resultSet.getString("credit_limit"));
                customers.setIsActive(resultSet.getBoolean("is_active"));
                
                // 获取创建时间和更新时间
                if (resultSet.getTimestamp("created_time") != null) {
                    customers.setCreatedTime(resultSet.getTimestamp("created_time").toLocalDateTime());
                }
                if (resultSet.getTimestamp("updated_time") != null) {
                    customers.setUpdatedTime(resultSet.getTimestamp("updated_time").toLocalDateTime());
                }
                
                customersList.add(customers);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "搜索时发生错误: " + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return customersList;
    }

}
